package com.store.utils;


import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * @author yu
 * @date 2023/4/5 16:31
 * @des
 */

public class JDBCUtil {
    //1、私有化构造方法
    public JDBCUtil(){

    }

    //2、声明所需要的配置变量
    private static String driverClass;
    private static String url;
    private static String username;
    private static String password;

    private static Connection con;
    //3、提供静态代码块，赋值，注册驱动

    static {
        try {
            //读取配置文件的信息为变量赋值
            InputStream resourceAsStream = JDBCUtil.class.getClassLoader().getResourceAsStream("config.properties");
            Properties prop = new Properties();
            prop.load(resourceAsStream);
            driverClass=prop.getProperty("driverClass");
            url=prop.getProperty("url");
            username=prop.getProperty("username");
            password=prop.getProperty("password");
            //注册驱动
            Class.forName(driverClass);

        } catch (IOException e) {
            throw new RuntimeException(e);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }
    //4、连接数据库
    public static Connection getConnection(){
        Connection con= null;
        try {
            if (con==null){
                con = DriverManager.getConnection(url, username, password);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return con;
    }

    public static void close(Connection con, Statement stat, ResultSet rs)  {
        if (con!=null){
            try {
                con.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (stat!=null){
            try {
                stat.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

    }

    // 返回 列表
    public  <T> List<T> list(Class<T> tClass){
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<T> result = new ArrayList<>();
        try {
            // 获取类的所有属性，非父属性
            Field[] fields = tClass.getDeclaredFields();
            StringBuilder sql = new StringBuilder("select ");
            for (int i = 0; i < fields.length; i++){
                Field field = fields[i];
                field.setAccessible(true);
                sql.append(field.getName());
                if (i != fields.length - 1){
                    sql.append(", ");
                }
            }
            sql.append(" from " + tClass.getSimpleName());
            preparedStatement = connection.prepareStatement(sql.toString());
            System.out.println(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                T t = tClass.newInstance();
                for (Field field : fields){
                    String fieldName = field.getName();
                    //用实体类的set方法赋值
                    String methodName = "set" + fieldName.substring(0,1).toUpperCase() + fieldName.substring(1);
                    Method method = tClass.getMethod(methodName, field.getType());
                    Object value = resultSet.getObject(fieldName);
                    method.invoke(t, value);
                }
                result.add(t);
            }
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            close(connection, preparedStatement, resultSet);
        }
        return result;
    }

    // 增加 通过传入的对象，不是null的属性插入数据库
    public <T> boolean insert(T source){
        Connection connection = getConnection();
        Statement statement = null;
        try {
            Class<?> aClass = source.getClass();
            StringBuilder sql = new StringBuilder("insert into ");
            String tableName = aClass.getSimpleName();
            sql.append(tableName + "(");
            Field[] fields = aClass.getDeclaredFields();
            for (int i = 0; i < fields.length; i++){
                Field field = fields[i];
                field.setAccessible(true);
                String fieldName = field.getName();
                sql.append(" " + fieldName);
                if (i != fields.length - 1){
                    sql.append(",");
                }
            }
            sql.append(") value(");
            for (int i = 0; i < fields.length; i++){
                Field field = fields[i];
                String fieldName = field.getName();
                String methodName = "get" + fieldName.substring(0,1).toUpperCase() + fieldName.substring(1);
                Method method = aClass.getMethod(methodName);
                Object value = method.invoke(source);
                if (value != null && String.class == value.getClass()){
                    sql.append(" ' ").append(value).append(" '");
                } else {
                    sql.append(" " + value);
                }
                if (i != fields.length -1){
                    sql.append(",");
                }
            }
            sql.append(")");
            System.out.println(sql);
            statement = connection.createStatement();
            int i = statement.executeUpdate(sql.toString());
            if (i == 0){
                return false;
            }
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(connection, statement, null);
        }
        return true;
    }
    // 删除 通过对象的id删除
    public  <T> boolean deleteById(T source){
        Connection connection = null;
        Statement statement = null;
        try {
            Class<?> aClass = source.getClass();
            connection = getConnection();
            String tableName = aClass.getSimpleName();
            statement = connection.createStatement();
            StringBuilder sql = new StringBuilder("delete from " + tableName + " where id = ");
            String methodName = "getId";
            Method method = aClass.getMethod(methodName);
            Object value = method.invoke(source);
            sql.append(value);
            int i = statement.executeUpdate(sql.toString());
            if (i != 0){
                return false;
            }
        } catch (SQLException | NoSuchMethodException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } finally {
            close(connection, statement, null);
        }
        return true;
    }
    // 修改 通过id，和其他属性修改
    public  <T> boolean update(T source){
        Connection connection = null;
        Statement statement = null;
        try {
            connection = getConnection();
            statement = connection.createStatement();
            Class<?> aClass = source.getClass();
            String tableName = aClass.getSimpleName();
            StringBuilder sql = new StringBuilder("update "+ tableName + " set ");
            Field[] fields = aClass.getDeclaredFields();
            Integer id = -1;
            int total = 0;
            for (int i = 0; i < fields.length; i++){
                Field field = fields[i];
                field.setAccessible(true);
                String fieldName = field.getName();
                String methodName = "get" + fieldName.substring(0,1).toUpperCase() + fieldName.substring(1);
                if (fieldName.equals("id")){
                    Method method = aClass.getMethod(methodName);
                    Object value = method.invoke(source);
                    id = (Integer) value;
                    continue;
                }
                Method method = aClass.getMethod(methodName);
                Object value = method.invoke(source);
                if (value != null){
                    if (String.class == value.getClass()){
                        if (total == 0){
                            sql.append(" " + fieldName + " = '" + value + "'");
                        } else {
                            sql.append(" , " + fieldName + " = '" + value + "'");
                        }
                    } else {
                        if (total == 0){
                            sql.append(" " + fieldName + " = " + value);
                        } else {
                            sql.append(" , " + fieldName + " = " + value);
                        }
                    }
                    total++;
                }
            }
            sql.append(" where id = ").append(id);
            System.out.println(sql);
            int i = statement.executeUpdate(sql.toString());
            if (i != 0){
                return true;
            }
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, statement, null);
        }
        return false;
    }
    // 通过 实体类的属性值，不是null查询
    public  <T> T query(T source){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = getConnection();
            statement = connection.createStatement();
            Class<?> aClass = source.getClass();
            String tableName = aClass.getSimpleName();
            StringBuilder sql = new StringBuilder("select * from " + tableName + " where ");
            Field[] fields = aClass.getDeclaredFields();
            for (int i = 0; i < fields.length; i++){
                Field field = fields[i];
                field.setAccessible(true);
                String fieldName = field.getName();
                String methodName = "get" + fieldName.substring(0,1).toUpperCase() + fieldName.substring(1);
                Method method = aClass.getMethod(methodName);
                Object value = method.invoke(source);
                if (value != null){
                    if (String.class == value.getClass()){
                        if (i == 0){
                            sql.append(" " + fieldName + " = '" + value + "'");
                        } else {
                            sql.append(" and " + fieldName + " = '" + value + "'");
                        }
                    } else {
                        if (i == 0){
                            sql.append(" " + fieldName + " = " + value);
                        } else {
                            sql.append(" and " + fieldName + " = " + value);
                        }
                    }
                }
                resultSet = statement.executeQuery(sql.toString());
                while (resultSet.next()){
                    T t = (T) source.getClass().newInstance();
                    for (Field field1 : fields){
                        String field1Name = field1.getName();
                        //用实体类的set方法赋值
                        String method1Name = "set" + field1Name.substring(0,1).toUpperCase() + field1Name.substring(1);
                        Method method1 = aClass.getMethod(method1Name, field1.getType());
                        Object v = resultSet.getObject(field1Name);
                        method1.invoke(t, v);
                    }
                    return t;
                }
            }
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } finally {
            close(connection, statement, null);
        }
        return null;
    }
}

